package com.eascs.admin.o2o.generation.service;

import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.eascs.admin.o2o.generation.dao.O2oGenerationDao;
import com.eascs.admin.o2o.generation.entity.O2oGenerationEntity;
import com.eascs.admin.o2o.generation.vo.O2oGenerationVo;
import com.eascs.admin.o2o.loan.entity.O2oPlatformRegional;
import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.brand.dao.O2oBrandDao;

@Service
public class O2oGenerationService {

	@Autowired
	O2oGenerationDao generationDao;
	@Autowired
	O2oBrandDao o2oBrandDao;

	public PageResult<Object[]> findByPage(O2oGenerationVo vo) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, vo.getPageSize(), vo.getPageIndex());
		StringBuffer querySql = new StringBuffer();
		querySql.append(" select ogr.id,ogr.loan_apply_no,ogr.bill_num,ogr.loan_bill_code ");
		querySql.append(" ,ogr.loan_user_name,ba.loan_date,ogr.oper_name,ogr.province_name ");
		querySql.append(" ,ogr.ltd_name,ogr.play_money,ogr.play_time ");
		querySql.append(" ,ogr.create_time,ogr.flow_status from o2o_generation_repayment ogr ");
		querySql.append(" LEFT JOIN o2o_bank_loan_data ba on ogr.loan_bill_code=ba.bill_code ");// o2o_bank_loan_data--ba.loan_date放款日期
		querySql.append(" where /**DATA_PERMISSION(ogr.LTD_ID)**/ ogr.is_delete='N' ");
		if (!StringUtils.isEmpty(vo.getBillNum())) {
			querySql.append(" and ogr.bill_num like ? ");
			sqlPageQuery.addLikeParam(vo.getBillNum());
		}
		if (!StringUtils.isEmpty(vo.getLoanBillCode())) {
			querySql.append(" and ogr.loan_bill_code like ? ");
			sqlPageQuery.addLikeParam(vo.getLoanBillCode());
		}
		if (!StringUtils.isEmpty(vo.getLoanUserName())) {
			querySql.append(" and ogr.loan_user_name like ? ");
			sqlPageQuery.addLikeParam(vo.getLoanUserName());
		}
		if (!StringUtils.isEmpty(vo.getProvinceCode())) {
			querySql.append(" and ogr.province_code = ? ");
			sqlPageQuery.addParam(vo.getProvinceCode());
		}
		if (!StringUtils.isEmpty(vo.getOperName())) {
			querySql.append(" and ogr.oper_name like ? ");
			sqlPageQuery.addLikeParam(vo.getOperName());
		}
		if (!StringUtils.isEmpty(vo.getLtdName())) {
			querySql.append(" and ogr.ltd_name like ? ");
			sqlPageQuery.addLikeParam(vo.getLtdName());
		}
		if (!StringUtils.isEmpty(vo.getCreateStartTime())) {
			querySql.append(" and ogr.create_time >= ? ");
			sqlPageQuery.addParam(vo.getCreateStartTime());
		}
		if (!StringUtils.isEmpty(vo.getCreateEndTime())) {
			querySql.append(" and ogr.create_time <= ? ");
			sqlPageQuery.addParam(vo.getCreateEndTime());
		}
		if (!StringUtils.isEmpty(vo.getFlowStatus())) {
			querySql.append(" and ogr.flow_status = ? ");
			sqlPageQuery.addParam(vo.getFlowStatus());
		}
		querySql.append(" ORDER BY ogr.create_time ");
		sqlPageQuery.setQuerySql(querySql.toString());
		return generationDao.findDataWithPage(sqlPageQuery);
	}
	
	public PageResult<Object[]> findByPageExport(O2oGenerationVo vo) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, vo.getPageSize(), vo.getPageIndex());
		StringBuffer querySql = new StringBuffer();
		querySql.append(" select ogr.bill_num,ogr.loan_bill_code ");
		querySql.append(" ,ogr.loan_user_name,DATE_FORMAT(ogr.loan_time,'%Y-%m-%d'),ogr.oper_name,ogr.province_name ");
		querySql.append(" ,ogr.ltd_name,ogr.play_money,DATE_FORMAT(ogr.play_time,'%Y-%m-%d') ");
		querySql.append(" ,DATE_FORMAT(ogr.create_time,'%Y-%m-%d'), ");
		querySql.append(" (case when ogr.flow_status='NEW' then '新建' ");
		querySql.append(" when ogr.flow_status='RUN' then '审核中' ");
		querySql.append(" when ogr.flow_status='CLO' then '流程结束' end) as flow_status ");
		querySql.append(" from o2o_generation_repayment ogr ");
		querySql.append(" where /**DATA_PERMISSION(ogr.LTD_ID)**/ ogr.is_delete='N' ");
		if (!StringUtils.isEmpty(vo.getBillNum())) {
			querySql.append(" and ogr.bill_num like ? ");
			sqlPageQuery.addLikeParam(vo.getBillNum());
		}
		if (!StringUtils.isEmpty(vo.getLoanBillCode())) {
			querySql.append(" and ogr.loan_bill_code like ? ");
			sqlPageQuery.addLikeParam(vo.getLoanBillCode());
		}
		if (!StringUtils.isEmpty(vo.getLoanUserName())) {
			querySql.append(" and ogr.loan_user_name like ? ");
			sqlPageQuery.addLikeParam(vo.getLoanUserName());
		}
		if (!StringUtils.isEmpty(vo.getProvinceCode())) {
			querySql.append(" and ogr.province_code = ? ");
			sqlPageQuery.addParam(vo.getProvinceCode());
		}
		if (!StringUtils.isEmpty(vo.getOperName())) {
			querySql.append(" and ogr.oper_name like ? ");
			sqlPageQuery.addLikeParam(vo.getOperName());
		}
		if (!StringUtils.isEmpty(vo.getCreateStartTime())) {
			querySql.append(" and ogr.create_time >= ? ");
			sqlPageQuery.addParam(vo.getCreateStartTime());
		}
		if (!StringUtils.isEmpty(vo.getCreateEndTime())) {
			querySql.append(" and ogr.create_time <= ? ");
			sqlPageQuery.addParam(vo.getCreateEndTime());
		}
		if (!StringUtils.isEmpty(vo.getFlowStatus())) {
			querySql.append(" and ogr.flow_status = ? ");
			sqlPageQuery.addParam(vo.getFlowStatus());
		}
		querySql.append(" ORDER BY ogr.create_time ");
		sqlPageQuery.setQuerySql(querySql.toString());
		return generationDao.findDataWithPage(sqlPageQuery);
	}

	@Transactional("o2oTransactionManager")
	public int insert(O2oGenerationEntity entity) {
		return generationDao.insert(entity);
	}

	@Transactional("o2oTransactionManager")
	public int update(O2oGenerationEntity entity) {
		return generationDao.updateById(entity);
	}
	
	public O2oGenerationEntity getById(String id){
		return generationDao.getById(O2oGenerationEntity.class, id);
	}

	public O2oGenerationEntity findById(String id) {
		String sql = "select ogr.* from o2o_generation_repayment ogr where /**DATA_PERMISSION(ogr.LTD_ID)**/ id = ? ";
		return generationDao.findEntity(sql, O2oGenerationEntity.class, id);
	}

	public List<Object[]> finds(String code) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ol.loanapply_no,ol.bill_code,CONCAT(ol.bill_code,'-',olu.user_full_name) ");
		sql.append(" ,olu.user_full_name,ol.payment_time,ol.loan_term,ol.settle_date ");
		sql.append(" from o2o_loanapply ol LEFT JOIN o2o_loan_user olu ");
		sql.append(" on ol.loan_id = olu.loan_id where /**DATA_PERMISSION(ol.LTD)**/ ");
		sql.append(" olu.spouse_id is null and olu.loanapply_no=ol.loanapply_no  ");
		sql.append(" and ol.wf_type in('O','YQ') and ol.bill_code like ? GROUP BY ol.bill_code LIMIT 10");
		return generationDao.findObjectsList(sql.toString(), "%" + code + "%");
	}
	
	public List<Object[]> findscommon(String code) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ol.loanapply_no,ol.bill_code,CONCAT(ol.bill_code,'-',olu.user_full_name) ");
		sql.append(" ,olu.user_full_name,ol.payment_time,ol.loan_term,ol.end_bank_date ");
		sql.append(" from o2o_loanapply ol LEFT JOIN o2o_loan_user olu ");
		sql.append(" on ol.loan_id = olu.loan_id where /**DATA_PERMISSION(ol.LTD)**/ ");
		sql.append(" olu.spouse_id is null and olu.loanapply_no=ol.loanapply_no  ");
		sql.append(" and ol.bill_code like ? GROUP BY ol.bill_code LIMIT 10");
		return generationDao.findObjectsList(sql.toString(), "%" + code + "%");
	}
	
	public O2oPlatformRegional findLtd(String ltd){
		String sql = "select oll.* from o2o_platform_regional oll where oll.platform_id=?";
		return generationDao.findEntity(sql, O2oPlatformRegional.class, ltd);
	}
}
